package com.cs.beneficios.business.entity;

import com.cs.beneficios.business.model.ProgramaVO;
import com.cs.beneficios.common.ConnectionFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;



public class ProgramaDAO 
{

  /**
   * Conexion a la base de datos.
   */
  private Connection conn = null;
  private ConnectionFactory cf;

  /**
   * Constructor que inicializa la conexion con la base de datos.
   * @param conn Conexion con la base de datos.
   */
  
  public ProgramaDAO(ConnectionFactory cf)
  {
    this.cf = cf;
    try{
      this.conn = this.cf.getConnection();
    }catch(SQLException e){}
  }
  
  public Collection findProgramaByCriterea(ProgramaVO criterea) throws SQLException 
  {
    String query = "SELECT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "+
                   "pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, "+
                   "pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "+
                   "usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO "+
                   "FROM TCS_BEN_PROGRAMA pro, "+
                   "TCS_BEN_AREA area, "+
                   "paoyer.TCS_USUARIO usu "+
                   "WHERE Fcs_Ben_Val_Vigencia(pro.VIGENCIA, pro.DESDE_VIGENCIA, pro.HASTA_VIGENCIA, sysdate) = 'A' "+
                   "AND pro.AREA_CODIGO = area.CODIGO "+
                   "AND pro.USU_CODIGO = usu.CODIGO";
                          
    if(criterea.getCode() != null)
    {
      query += " AND pro.CODIGO = "+criterea.getCode()+" ";
    }
    if(!criterea.getNombre().equalsIgnoreCase(""))
    {
      query += " AND pro.NOMBRE LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
    }
    if(criterea.getCodeArea() != null)
    {
      query += " AND area.CODIGO = "+criterea.getCodeArea();
    }
    
    query += " ORDER BY pro.NOMBRE";
    
    ResultSet rst = null;
    ArrayList programas = new ArrayList();
    rst = cf.getRecordsRset(query,null,conn);
    ProgramaVO pro = null;
    while ( rst.next() ) 
    {
        pro = new ProgramaVO();
        pro.setCode(new Long(rst.getLong( "COD_PROGRAMA"))); 
        pro.setNombre(rst.getString( "NOM_PROGRAMA"));
        pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
        pro.setObjetivo(rst.getString("OBJETIVO"));
        pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
        pro.setCodeArea(new Long(rst.getString("COD_AREA")));
        pro.setNombreArea(rst.getString("NOM_AREA"));
        pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
        pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
        programas.add(pro);
        pro=null;
    }
    rst.close();
    return programas;
  }
  
    public Collection findAllProgramaByCriterea(ProgramaVO criterea) throws SQLException 
    {
      String query = "SELECT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "+
                     "pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, "+
                     "pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "+
                     "usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO "+
                     "FROM TCS_BEN_PROGRAMA pro, "+
                     "TCS_BEN_AREA area, "+
                     "paoyer.TCS_USUARIO usu "+
                     "WHERE pro.AREA_CODIGO = area.CODIGO "+
                     "AND pro.USU_CODIGO = usu.CODIGO";
                            
      if(criterea.getCode() != null)
      {
        query += " AND pro.CODIGO = "+criterea.getCode()+" ";
      }
      if(!criterea.getNombre().equalsIgnoreCase(""))
      {
        query += " AND pro.NOMBRE LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
      }
      if(criterea.getCodeArea() != null)
      {
        query += " AND area.CODIGO = "+criterea.getCodeArea();
      }
      
      query += " ORDER BY pro.NOMBRE";
      
      ResultSet rst = null;
      ArrayList programas = new ArrayList();
      rst = cf.getRecordsRset(query,null,conn);
      ProgramaVO pro = null;
      while ( rst.next() ) 
      {
          pro = new ProgramaVO();
          pro.setCode(new Long(rst.getLong( "COD_PROGRAMA"))); 
          pro.setNombre(rst.getString( "NOM_PROGRAMA"));
          pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
          pro.setObjetivo(rst.getString("OBJETIVO"));
          pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
          pro.setCodeArea(new Long(rst.getString("COD_AREA")));
          pro.setNombreArea(rst.getString("NOM_AREA"));
          pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
          pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
          programas.add(pro);
          pro=null;
      }
      rst.close();
      return programas;
    }
  
  
  /**
   * 
   * @param criterea bean with all data to filter the query.
   * @param codeUser code of the user at the moment connected 
   * @param groupUserName Name of the user group
   * @param areaUser Code of the area to which the user belongs
   * @notes This method returns all the programs to which a user has permission, as long as they are associate to a seat
   * @author Carlos Ruiz & Daniel Monsalve 
   * @date 04/05/2007
   * @return Collection with the programs
   * @throws java.sql.SQLException
   */
  public Collection findProgramaByCriterea(ProgramaVO criterea, Long codeUser, String groupUserName, Long areaUser) throws SQLException 
  {
    String query = "select DISTINCT COD_PROGRAMA, NOM_PROGRAMA, DESC_PROGRAMA, OBJETIVO, POBLACION_OBJETIVO, COD_AREA, NOM_AREA, COD_USUARIO, NOM_USUARIO  from VCS_BEN_PROGRAMAS  ";
    
    if((Object)codeUser != null)
    {
      query +=" where ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' or admSubpro = "+codeUser+
            "          or admProg = "+codeUser ;
            if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
            {
              query += " or COD_AREA = "+areaUser;
            }
            query += " ) ";
    }else
    {
      return null;
    }
                         
    if(criterea.getCode() != null)
    {
      query += " AND COD_PROGRAMA = "+criterea.getCode()+" ";
    }
    if(!criterea.getNombre().equalsIgnoreCase(""))
    {
      query += " AND NOM_PROGRAMA LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
    }
    if(criterea.getCodeArea() != null)
    {
      query += " AND COD_AREA = "+criterea.getCodeArea();
    }
    
    query += " ORDER BY NOM_PROGRAMA";
    ResultSet rst = null;
    ArrayList programas = new ArrayList();
    rst = cf.getRecordsRset(query,null,conn);
    ProgramaVO pro = null;
    while ( rst.next() ) 
    { 
        pro = new ProgramaVO();
        pro.setCode(new Long(rst.getLong("COD_PROGRAMA"))); 
        pro.setNombre(rst.getString("NOM_PROGRAMA"));
        pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
        pro.setObjetivo(rst.getString("OBJETIVO"));
        pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
        pro.setCodeArea(new Long(rst.getString("COD_AREA")));
        pro.setNombreArea(rst.getString("NOM_AREA"));
        pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
        pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
        programas.add(pro);
        pro=null;
    }
    rst.close();
    return programas;
  }
  
  public Collection findProgramaByCritereaOB(ProgramaVO criterea, Long codeUser, String groupUserName, Long areaUser) throws SQLException 
  {
	String query = "select DISTINCT COD_PROGRAMA, NOM_PROGRAMA, DESC_PROGRAMA, OBJETIVO, POBLACION_OBJETIVO, COD_AREA, NOM_AREA, COD_USUARIO, NOM_USUARIO  from VCS_BEN_PROGRAMAS ";
      
	if((Object)codeUser != null)
	{
	  query +=" where ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' or admSubpro = "+codeUser+
			"          or admProg = "+codeUser+ " or ususede ="+codeUser;
			if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
			{
			  query += " or COD_AREA = "+areaUser;
			}
			query += " ) ";
	}else
	{
	  return null;
	}
                           
	if(criterea.getCode() != null)
	{
	  query += "AND COD_PROGRAMA = "+criterea.getCode()+" ";
	}
	if(!criterea.getNombre().equalsIgnoreCase(""))
	{
	  query += "AND NOM_PROGRAMA LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
	}
	if(criterea.getCodeArea() != null)
	{
	  query += "AND COD_AREA = "+criterea.getCodeArea();
	}
      
	query += " ORDER BY NOM_PROGRAMA";
	//System.out.println(query);
	ResultSet rst = null;
	ArrayList programas = new ArrayList();
	rst = cf.getRecordsRset(query,null,conn);
	ProgramaVO pro = null;
	while ( rst.next() ) 
	{
		pro = new ProgramaVO();
		pro.setCode(new Long(rst.getLong( "COD_PROGRAMA"))); 
		pro.setNombre(rst.getString( "NOM_PROGRAMA"));
		pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
		pro.setObjetivo(rst.getString("OBJETIVO"));
		pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
		pro.setCodeArea(new Long(rst.getString("COD_AREA")));
		pro.setNombreArea(rst.getString("NOM_AREA"));
		pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
		pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
		programas.add(pro);
		pro=null;
	}
	rst.close();
	return programas;
  }
  
  public Collection findProgramaWithoutSede(ProgramaVO criterea, Long codeUser, String groupUserName, Long areaUser) throws SQLException 
  {
    String query = "SELECT DISTINCT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "+
                   "pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, pro.POBLACION_OBJETIVO, "+
                   "area.CODIGO COD_AREA, area.NOMBRE NOM_AREA,	usu.CODIGO COD_USUARIO, "+
                   "usu.NOMBRE NOM_USUARIO, a.admProg "+
                   "FROM VCS_BEN_SUBPROG_WITHOUT_SEDE a, TCS_BEN_PROGRAMA pro, "+
                   "TCS_BEN_AREA area, paoyer.TCS_USUARIO usu, paoyer.tcs_grupo_usuario gus "+
                   "WHERE  gus.CODIGO = usu.GUS_CODIGO "+
                   "AND a.COD_PROGRAMA = pro.CODIGO "+
                   "AND Fcs_Ben_Val_Vigencia(pro.VIGENCIA, pro.DESDE_VIGENCIA, pro.HASTA_VIGENCIA, SYSDATE) = 'A' "+
                   "AND pro.AREA_CODIGO = area.CODIGO "+
                   "AND pro.USU_CODIGO = usu.CODIGO "; 
    
    if((Object)codeUser != null)
    {
      query +=" AND ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' OR admSubpro = "+codeUser+
            "          OR pro.USU_CODIGO = "+codeUser ;
            if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
            {
              query += " OR area.Codigo = "+areaUser;
            }
            query += " ) ";
    }else
    {
      return null;
    }
                         
    if(criterea.getCode() != null)
    {
      query += "AND COD_PROGRAMA = "+criterea.getCode()+" ";
    }
    if(!criterea.getNombre().equalsIgnoreCase(""))
    {
      query += "AND NOM_PROGRAMA LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
    }
    if(criterea.getCodeArea() != null)
    {
      query += "AND COD_AREA = "+criterea.getCodeArea();
    }
    
    query += " ORDER BY NOM_PROGRAMA";
    //System.out.println(query);
    ResultSet rst = null;
    ArrayList programas = new ArrayList();
    rst = cf.getRecordsRset(query,null,conn);
        ProgramaVO pro = null;
        while ( rst.next() ) 
        {
        pro = new ProgramaVO();
        pro.setCode(new Long(rst.getLong( "COD_PROGRAMA"))); 
        pro.setNombre(rst.getString( "NOM_PROGRAMA"));
        pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
        pro.setObjetivo(rst.getString("OBJETIVO"));
        pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
        pro.setCodeArea(new Long(rst.getString("COD_AREA")));
        pro.setNombreArea(rst.getString("NOM_AREA"));
        pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
        pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
        programas.add(pro);
        pro=null;
        }
  	rst.close();
    return programas;
  }
  
  /**
   * 
   * @param criterea bean with all data to filter the query.
   * @param groupUserName Name of the user group
   * @param codeUser code of the user at the moment connected
   * @param Code of the area to which the user belongs
   * @notes This method returns all the programs to which a user has permission.
   * @return Collection with the programs
   * @throws java.sql.SQLException
   */
  public Collection findProgramaByCriterea(ProgramaVO criterea, String groupUserName, Long codeUser, Long areaUser) throws SQLException 
  {
    ArrayList programas = new ArrayList();
    String query = "select  upper(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "+
                   "COD_AREA codArea, NOM_AREA nombreArea "+
                   " from VCS_BEN_SOLO_PROGRAMAS_EST_VIG ";
                   
    if(!groupUserName.equalsIgnoreCase("") && codeUser != null)
    {
      query +=" where ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' " +
            "          or COD_USUARIO = "+codeUser ;
            if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
            {
              query += " or COD_AREA = "+areaUser;
            }
            query += " ) ";
    }
        if(!criterea.getNombre().equalsIgnoreCase(""))
      {
        query += " AND NOM_PROGRAMA LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
      }
      
      if(criterea.getCodePrograma() != null && !criterea.getCodePrograma().equalsIgnoreCase(""))
      {
        query += " AND COD_PROGRAMA = "+criterea.getCodePrograma().trim()+" ";
      }
      
      if(criterea.getCodeArea() != null)
      {
        query += " AND COD_AREA = "+criterea.getCodeArea();
      }
      
      if(criterea.getVigencia() != null)
      {
        query += " and (vigencia = '"+criterea.getVigencia()+"' OR sysdate > HASTA_VIGENCIA) "; 
      }
      
      query += " ORDER BY NOM_PROGRAMA";
      //System.out.println(query);
      ResultSet rst = null;
      rst = cf.getRecordsRset(query,null,conn);
      ProgramaVO pro = null;
      while ( rst.next() ) 
      {
          pro = new ProgramaVO();
          pro.setCode(new Long(rst.getLong( "CODIGO"))); 
          pro.setNombre(rst.getString( "PROGRAMA"));
          pro.setCodeArea(new Long(rst.getString("CODAREA")));
          pro.setNombreArea(rst.getString("NombreAREA"));
          programas.add(pro);
          pro=null;
      }
      rst.close();
    
    return programas;
  }
  
  //Método que ejecuta la búsqueda de programas para el reporte detallado
  //de beneficios otorgados
   public Collection findProgramaByCritereaReport(ProgramaVO criterea, String groupUserName, Long codeUser, Long areaUser) throws SQLException 
   {
	 ArrayList programas = new ArrayList();
	 String query = "select  upper(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "+
					"COD_AREA codArea, NOM_AREA nombreArea "+
					" from VCS_BEN_SOLO_PROGRAMAS_EST_VIG v ";
                    
	 if(!groupUserName.equalsIgnoreCase("") && codeUser != null)
	 {
	   query +=" where ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' " +
			 " or COD_PROGRAMA=any(select pro.codigo " + 
			 "from TCS_BEN_PROGRAMA pro " + 
			 "where pro.usu_codigo="+codeUser+  
			 " union select sub.prg_codigo " + 
			 "from tcs_ben_subprograma sub " + 
			 "where sub.usu_codigo="+codeUser+  
			 " union select sub.prg_codigo " + 
			 "from tcs_ben_subprograma sub, tcs_ben_subprograma_usuario subus " + 
			 "where sub.CODIGO = subus.SBPR_CODIGO " + 
			 "and subus.usu_codigo="+codeUser+")";
			 if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
			 {
			   query += " or COD_AREA = "+areaUser;
			 }
			 query += " ) ";
	 }
		/* if(!criterea.getNombre().equalsIgnoreCase(""))
	   {
		 query += " AND NOM_PROGRAMA LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
	   }*/
       
	  /* if(criterea.getCodePrograma() != null && !criterea.getCodePrograma().equalsIgnoreCase(""))
	   {
		 query += " AND COD_PROGRAMA = "+criterea.getCodePrograma().trim()+" ";
	   }*/
       
	   if(criterea.getCodeArea() != null)
	   {
		 query += " AND COD_AREA = "+criterea.getCodeArea();
	   }
       
	   if(criterea.getVigencia() != null)
	   {
		 query += " and (vigencia = '"+criterea.getVigencia()+"' OR sysdate > HASTA_VIGENCIA) "; 
	   }
       
	   query += " ORDER BY NOM_PROGRAMA";
//	   System.err.println(query);
	   ResultSet rst = null;
	   rst = cf.getRecordsRset(query,null,conn);
	   ProgramaVO pro = null;
	   while ( rst.next() ) 
	   {
		   pro = new ProgramaVO();
		   pro.setCode(new Long(rst.getLong( "CODIGO"))); 
		   pro.setNombre(rst.getString( "PROGRAMA"));
		   pro.setCodeArea(new Long(rst.getString("CODAREA")));
		   pro.setNombreArea(rst.getString("NombreAREA"));
		   programas.add(pro);
		   pro=null;
	   }
	   rst.close();
     
	 return programas;
   }
  
  
    public Collection findProgramaWithoutVigencia(String groupUserName, Long codeUser, Long areaUser) throws SQLException 
        {
          ArrayList programas = new ArrayList();
          String query = "select  DISTINCT upper(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "+
                         "COD_AREA codArea, NOM_AREA nombreArea "+
                         " from VCS_BEN_SOLO_PROGRAMAS ";
                         
          if(!groupUserName.equalsIgnoreCase("") && codeUser != null)
          {
            query +=" where ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' " +
                  "          or COD_USUARIO = "+codeUser ;
                  if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
                  {
                    query += " or COD_AREA = "+areaUser;
                  }
                  query += " ) ";
          }     
            query += " and (vigencia = 'I' OR sysdate > HASTA_VIGENCIA) ORDER BY Programa";
            //System.out.println(query);
            ResultSet rst = null;
            rst = cf.getRecordsRset(query,null,conn);
            ProgramaVO pro = null;
            while ( rst.next() ) 
            {
                pro = new ProgramaVO();
                pro.setCode(new Long(rst.getLong( "CODIGO"))); 
                pro.setNombre(rst.getString( "PROGRAMA"));
                pro.setCodeArea(new Long(rst.getString("CODAREA")));
                pro.setNombreArea(rst.getString("NombreAREA"));
                programas.add(pro);
                pro=null;
            }
            rst.close();
          
          return programas;
        }
  
  /**
   * Autor DORIS
   * @param codePrograma
   * @return 
   */
  
  public boolean EstadoPrograma(ProgramaVO criterea){
  
    String query = "select  codigo from tcs_ben_programa where  vigencia = 'I' and "+
                   " codigo = "+criterea.getCodePrograma()  ;
                   
    ResultSet rst = null;
    
    try{
    rst = cf.getRecordsRset(query,null,conn);
    
      if(rst.next() ){
          return true; 
      }else{
          return false;
      }
    
    }catch(Exception e){
		System.err.println("/******** TDSUB - cwsub");
		e.printStackTrace();
		System.err.println("TDSUB - cwsub ********/");
      return false;
    }
    
  }
   
/**
   * Autor DORIS
   * @param codePrograma
   * @return 
   */
   
    public int getActivarProg(ProgramaVO criterea){
    
    String strSql = " update tcs_ben_programa set vigencia = 'A' , comentario = '', hasta_vigencia=hasta_vigencia+TO_NUMBER(hasta_vigencia-desde_vigencia) "+
                    " where codigo = "+criterea.getCodePrograma(); 
    int modificar = 0;
    
    try{
     modificar = cf.execSql(strSql,null,conn);
    }catch(Exception e){
		System.err.println("/******** TDSUB - cwsub");
		e.printStackTrace();
		System.err.println("TDSUB - cwsub ********/");
    }
      return modificar;
    }
  
  public Collection getProgramasVigentes(ProgramaVO criterea, String groupUserName, Long codeUser, Long areaUser, boolean controlAcceso) throws SQLException
  {
     ArrayList programas = new ArrayList();
     String query = "select  upper(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "+
                    "COD_AREA codArea, NOM_AREA nombreArea "+
                    " from VCS_BEN_SOLO_PROGRAMAS_EST_VIG ";
                   
     if(!groupUserName.equalsIgnoreCase("") && codeUser != null)
     {
        query +=" where ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' " +
             "          or COD_USUARIO = "+codeUser ;
        if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
        {
            query += " or COD_AREA = "+areaUser;
        }
        
        query += " ) ";
     }
     
     if(criterea != null){
         if(criterea.getCodePrograma() != null && !criterea.getCodePrograma().equalsIgnoreCase(""))
         {
           query += " AND COD_PROGRAMA = "+criterea.getCodePrograma().trim()+" ";
         }    
     }
          
     if(controlAcceso && areaUser != null && areaUser.longValue() != 0)
     {
       query += "AND COD_AREA = "+areaUser.longValue();
     }
     
     query += " ORDER BY NOM_PROGRAMA";
     //System.out.println(query);
     ResultSet rst = null;
     rst = cf.getRecordsRset(query,null,conn);
     ProgramaVO pro = null;
     while ( rst.next() ) 
     {
         pro = new ProgramaVO();
         pro.setCodePrograma(rst.getString("CODIGO"));
         pro.setNombre(rst.getString( "PROGRAMA"));
         pro.setCodeArea(new Long(rst.getString("CODAREA")));
         pro.setNombreArea(rst.getString("NombreAREA"));
         programas.add(pro);
         pro=null;
     }
     rst.close();
    
    return programas;


  }
  
  public String getNameByCode(String strCodeProg) throws SQLException
  {
    String query = "SELECT nombre FROM tcs_ben_programa "+
                   "WHERE codigo = "+strCodeProg;
    
    String strName = null;
    ResultSet rst = null;
    rst = cf.getRecordsRset(query,null,conn);
    if(rst.next())
    {
      strName = rst.getString("nombre");
    }
    rst.close();
    return strName;
  }
  
    public Collection findProgramaByCritereaWithGestor(ProgramaVO criterea, String groupUserName, Long codeUser, Long areaUser) throws SQLException 
    {
      ArrayList programas = new ArrayList();
      String query = "select  DISTINCT NOM_PROGRAMA Programa, COD_PROGRAMA Codigo, "+
                     "COD_AREA codArea, NOM_AREA nombreArea "+
                     " from VCS_BEN_SOLO_PROGRAMAS_EST_VIG, TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_SUBP_ATR_BENEFICIARIO SAB ";
                     
      if(!groupUserName.equalsIgnoreCase("") && codeUser != null)
      {
        query +=" where ('ADMINISTRADOR GENERAL' = '"+groupUserName+"' OR 'SUPER USUARIO' = '"+groupUserName+"' " +
              "          or COD_USUARIO = "+codeUser ;
              if(groupUserName != null && groupUserName.equals("ADMINISTRADOR SECRETARIA"))
              {
                query += " or COD_AREA = "+areaUser;
              }
              query += " ) ";
      }
          if(!criterea.getNombre().equalsIgnoreCase(""))
        {
          query += " AND NOM_PROGRAMA LIKE '%"+criterea.getNombre().toUpperCase().trim()+"%' ";
        }
        
        if(criterea.getCodePrograma() != null && !criterea.getCodePrograma().equalsIgnoreCase(""))
        {
          query += " AND COD_PROGRAMA = "+criterea.getCodePrograma().trim()+" ";
        }
        
        if(criterea.getCodeArea() != null)
        {
          query += " AND COD_AREA = "+criterea.getCodeArea();
        }
        
        if(criterea.getVigencia() != null)
        {
          query += " and (vigencia = '"+criterea.getVigencia()+"' OR sysdate > HASTA_VIGENCIA) "; 
        }
        
        query += " AND SUB.prg_codigo = COD_PROGRAMA AND SAB.SBPR_CODIGO = SUB.CODIGO AND UPPER(TRIM(SAB.NOMBRE)) = 'GESTOR' ";
        query += " ORDER BY NOM_PROGRAMA";
        //System.out.println(query);
        ResultSet rst = null;
        rst = cf.getRecordsRset(query,null,conn);
        ProgramaVO pro = null;
        while ( rst.next() ) 
        {
            pro = new ProgramaVO();
            pro.setCode(new Long(rst.getLong( "CODIGO"))); 
            pro.setNombre(rst.getString( "PROGRAMA").toUpperCase());
            pro.setCodeArea(new Long(rst.getString("CODAREA")));
            pro.setNombreArea(rst.getString("NombreAREA" ));
            programas.add(pro);
            pro=null;
        }
        rst.close();
      
      return programas;
    }
  
  public Connection getConn()
  {
    return conn;
  }
  
}